ajDBReadRecord function
Available since AlchemyJ v4.1
Description
The ajDBReadRecord function retrieves records in a database table satisfying the criteria specified in filter_condition. Please take note that to run this function from Excel, you would need to set up the Data Source Connection in ##ExternalResources.
Syntax
ajDBReadRecord(table_name, column_headers, [filter_type], [filter_condition], [sorting_criteria], [page_number], [rows_per_page], [table_schema], [transpose], [include_header], [data_source_id], [convert_to_text], [run_condition],[run_by_function_point_only] )
Argument Name | Argument Type | Description |
---|---|---|
table_name (required) | String | The name of the table to read from. |
column_headers (required) | Range / Array | The columns to be returned. It could be a range of single row or column where each cell is a table column name or a data name defined in DB Schema. CLOB and BLOB are not supported. |
filter_type (optional) | Double | The type of filtering. 3 types are supported 0, 1 and 2. |
filter_condition (optional) | Range / Array | The range that defines the filter condition. filter_type = 0. The range defines the WHERE clause of a SQL statement. The range will be concatenated into a single string. For example, Name = 'peter' and class = 'B'. filter_type = 1. The range defines the filter condition in the Kendo grid style. Click Insert Snippet\Filter Condition (Filter Type 1) to add the preset required format. filter_type =2. The range defines a filter condition similar to the format used in MS Query. Click Insert Snippet\Filter Condition (Filter Type 2) to add the preset required format. Refer to the Filter Condition snippet to see how you can use this snippet to define the required fields for filter type 1, 2. |
sorting_criteria (optional) | Range / Array | The range which defines the sorting criteria. Refer to the Sorting Criteria Snippet to see how to use this snippet to define the required fields. |
page_number (optional) | Double | The page of the returned records. It should be integer value, the decimal places will be rounded down if any. |
rows_per_page (optional) | Double | The number of rows per page of the returned records. It should be integer value, the decimal places will be rounded down if any. Default is 100. |
table_schema (optional) | Range / Array | The range that defines the DB Schema. |
transpose (optional) | Boolean | If it equals FALSE, the return records will not be transposed. If it equals TRUE, the returned records will be transposed from row to column. The default value is FALSE. |
include_header (optional) | Boolean | If it equals TRUE, the return result will include the column headers as the first row. If it equals FALSE, the return result will contain data only. The default value is FALSE. |
data_source_id (optional) | String | The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary". |
convert_to_text (optional) | Boolean | If it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type. The default value is FALSE. |
run_condition (optional) | Boolean | The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE. |
run_by_function_point_only (optional) | Boolean | If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE. |
The function will return:
1) Return Value: Table records in array format.
2) Return Type: Single Value / Multiple values (array formula)
Example
Before using the function, you need to set up the Data Source Connection in ##ExternalResources worksheet.
Before doing Preview Function Point and Generate API, you need to configure the Data Source ID in ##RestEndpointGroup worksheet or ##JavaApiClass.
We will use the following table in our examples. The table name is tb_customer. It has 5 columns and 5 rows.
Example 1 - Retrieve All Records
Retrieve all records from the table.
The values of parameters are as below, the column_header must be the actual column names of the database table:
As you can see, all records of the table are loaded to the selected area.
For the values of DOB, it is a number, you can add Data Dictionary and DB Schema to control the output format, detail refer to [Example 4](Example 4 - Filter Type is 2).
Example 2 - Filter Type is 0
Retrieve records where GENDER equals F.
The values of parameters are as below, input the where condition to filter_condition. Return the result with column name since include_header is TRUE.
Example 3 - Filter Type is 1
Retrieve record **where (DOB >= '1980-01-01') and (CUSTOMER_NAME = 'MERRY SO' or CUSTOMER_NAME = 'VICKY CHAN') ** and sort them by Name with descending order.
The filter condition table was created via AlchemyJ ribbon Insert Snippet and select Filter Condition (Filter Type 1) , make sure there are 5 empty rows, otherwise inserting Filter Condition will fail.
The Sorting Criteria table was created via AlchemyJ ribbon Insert Snippet and select Sorting Criteria.
The values of parameters are as below, more details about Filter Condition and Sorting Criteria, refer to Filter Condition and Sorting Criteria Snippet.
Example 4 - Filter Type is 2
Similar to Example 3, retrieve records where (DOB >= '1980-01-01' and CUSTOMER_NAME= 'MERRY SO') or (CUSTOMER_NAME = 'VICKY CHAN') using filter_type 2. Add DB Schema, table column names are mapped to the Data Name in DB Schema, you can use the Data Name to replace the column names of the database table, and define the Data Dictionary Address can format the output result.
Click Add Component to and select Data Dictionary to add the Data Dictionary .
The configuration of Data Dictionary is as below.
Click Add Component to and select DB Schema to add the DB Schema.
Configuration of Data Dictionary Address and Data Name columns are as below.
The values of parameters are as below. you can use the Data Name in DB Schema to replace the table column names in column_header, filter_condition and sorting_criteria.
The format of "Date of Birth" are formatted as yyyy-MM-dd.
Click here to download the use case workbooks for further reference.
Error Scenarios
It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will also raise error for below scenario(s).
Error Scenario |
---|
And/Or in filter condition is invalid. |
Column header is not a single row range or a single column range. |
Content in filter condition range does not meet the expected format of specified filter type. |
Data name in filter condition does not exist in the provided table schema. |
Data name in filter condition is empty. |
Data name is empty. |
DB connection error. |
Filter type is invalid, it must be 0, 1, or 2. |
Invalid operator value in filter condition. |
Invalid page number, it must be greater than or equal to 1. |
Invalid row per page, it must be greater than or equal to 1. |
Invalid sorting criteria range. |
Number of columns in column_headers does not match with the number of data columns. |
Operator in filter condition is empty. |
Sorting order in sorting criteria is not a number. |
Table name does not exist in provided DB Schema. |
Table name is empty. |
The provided column header cannot be found in the specified table schema range. |
The table schema columns are invalid. |
The table schema range does not include a header or a row of data. |
Total number of filter condition columns is not even number. |